﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;
public partial class ThietLapMuaBaoHiemNhanVien : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmMuaBaoHiemNhanVien";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static DataTable dt;
    static int PageNumber = 7;
    static int CurrentPage = 1;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {

                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    CurrentPage = 1;


                    Title = "Quản lý mua bảo hiểm nhân viên | " + Header;
                    btnCancel_Click(sender, e);
                    LoadTaiSan(dlnhanvien);
                    LoadNhaCungCap(dlBenBanBH, "--Chọn bên bán BH--");
                    LoadNhanVien(DropDownList2);
                    LoadTK();
                    LoadKhachHang(drkhach, "Chọn đơn vị nhân tiền bảo hiểm");
                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }

    void LoadKhachHang(DropDownList dl, string title)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_xdamuabaohiem_Listkhachhang");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem(title, ""));
        dl.SelectedIndex = 0;
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (f_Them.Equals(1))
        {
            try
            {
                Guid id = Guid.NewGuid();
                p = new SqlParameter[18];
                p[0] = new SqlParameter("@IDs", id);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@LuongCoBan", double.Parse(txtluongcoban.Text.Trim().Replace(",", "").Equals("") ? "0" : txtluongcoban.Text.Trim().Replace(",", "")));
                p[2].SqlDbType = SqlDbType.Float;
                p[3] = new SqlParameter("@PhuCap", double.Parse(txtphucap.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphucap.Text.Trim().Replace(",", "")));
                p[3].SqlDbType = SqlDbType.Float;
                p[4] = new SqlParameter("@PhanTramBaoHiem", double.Parse(txtphantrambaohiem.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantrambaohiem.Text.Trim().Replace(",", "")));
                p[4].SqlDbType = SqlDbType.Float;
                p[5] = new SqlParameter("@PhanTramCongTyNop", double.Parse(txtphantramcongty.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantramcongty.Text.Trim().Replace(",", "")));
                p[5].SqlDbType = SqlDbType.Float;
                p[6] = new SqlParameter("@PhanTramNhanVienNop", double.Parse(txtphantramnguoilaodong.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantramnguoilaodong.Text.Trim().Replace(",", "")));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@DonViBan", ex.GetGuid(dlBenBanBH.SelectedValue));
                p[7].SqlDbType = SqlDbType.UniqueIdentifier;
                p[8] = new SqlParameter("@DonViNhan", ex.GetGuid(drkhach.SelectedValue));
                p[8].SqlDbType = SqlDbType.UniqueIdentifier;
                p[9] = new SqlParameter("@NgayNop", ex.GetNgayThang(txtngaythang.Text.Trim()));
                p[9].SqlDbType = SqlDbType.SmallDateTime;
                p[10] = new SqlParameter("@HinhThuc", drhinhthucthanhtoan.SelectedValue);
                p[10].SqlDbType = SqlDbType.Int;
                p[11] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                p[11].SqlDbType = SqlDbType.UniqueIdentifier;
                p[12] = new SqlParameter("@NgayThu", ex.GetNgayThang(txtngaythu.Text.Trim()));
                p[12].SqlDbType = SqlDbType.SmallDateTime;
                p[13] = new SqlParameter("@DaTra", ckDaTra.Checked ? 1 : 2);
                p[13].SqlDbType = SqlDbType.Int;
                p[14] = new SqlParameter("@DaThu", ckDaThu.Checked ? 1 : 2);
                p[14].SqlDbType = SqlDbType.Int;
                p[15] = new SqlParameter("@Del", 1);
                p[15].SqlDbType = SqlDbType.Int;
                p[16] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[16].SqlDbType = SqlDbType.SmallDateTime;
                p[17] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[17].SqlDbType = SqlDbType.SmallDateTime;
                ex.ExecuteNonQuerry("sp_xdamuabaohiemnhanvien_Add", p);
                if (ckDaThu.Checked)
                {
                    Guid tlchi = Guid.NewGuid();
                    p = new SqlParameter[9];
                    p[0] = new SqlParameter("@IDs", tlchi);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                    p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[2] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[2].SqlDbType = SqlDbType.SmallDateTime;
                    p[3] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[3].SqlDbType = SqlDbType.Float;
                    p[4] = new SqlParameter("@NoiDung", DBNull.Value);
                    p[4].SqlDbType = SqlDbType.NVarChar;

                    p[5] = new SqlParameter("@NgayTao", DateTime.Now);
                    p[5].SqlDbType = SqlDbType.SmallDateTime;
                    p[6] = new SqlParameter("@NguoiTao", Session["user"].ToString());
                    p[6].SqlDbType = SqlDbType.NVarChar;
                    p[7] = new SqlParameter("@Del", 1);
                    p[7].SqlDbType = SqlDbType.Int;
                    p[8] = new SqlParameter("@IDThanhToan", id);
                    p[8].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAThietLapPhieuThuTienMat_Add", p);




                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NoiDung", "Nộp tiền mua bảo hiểm lại cho công ty");
                    p[1].SqlDbType = SqlDbType.NVarChar;
                    p[2] = new SqlParameter("@IDPhieuThu", tlchi);
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@Del", 1);
                    p[3].SqlDbType = SqlDbType.Int;
                    ex.ExecuteNonQuerry("sp_xdanoidungthutienmat_Add", p);

                    int sophieu;
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    dt = ex.GetData("sp_xdagetsophieuthuconnotravat_TienMat", p);
                    int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                    if (sopc == 0)
                        sophieu = 1;
                    else sophieu = sopc + 1;
                    p = new SqlParameter[11];
                    p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@NoiDung", DBNull.Value);
                    p[2].SqlDbType = SqlDbType.NVarChar;
                    p[3] = new SqlParameter("@DonVi", DBNull.Value);
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@SoPC", sophieu);
                    p[4].SqlDbType = SqlDbType.Int;
                    p[5] = new SqlParameter("@TaiKhoan", DBNull.Value);
                    p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[6] = new SqlParameter("@Del", 1);
                    p[6].SqlDbType = SqlDbType.Int;
                    p[7] = new SqlParameter("@LoaiCongNo", 2);
                    p[7].SqlDbType = SqlDbType.Int;
                    p[8] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[8].SqlDbType = SqlDbType.Float;
                    p[9] = new SqlParameter("@IDLenhChi", tlchi);
                    p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[10] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                    p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAPhieuChiCongNoThu_Add", p);

                }

                if (ckDaTra.Checked == true)
                {
                    if (drhinhthucthanhtoan.SelectedIndex == 1)
                    {
                        int sophieu;
                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythu.Text.Trim()));
                        p[0].SqlDbType = SqlDbType.SmallDateTime;
                        dt = ex.GetData("sp_xdagetsophieuchiconnotravat_NganHang", p);
                        int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                        if (sopc == 0)
                            sophieu = 1;
                        else sophieu = sopc + 1;

                        p = new SqlParameter[11];
                        p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythu.Text.Trim()));
                        p[1].SqlDbType = SqlDbType.SmallDateTime;
                        p[2] = new SqlParameter("@NoiDung", "Thanh toán tiền mua bảo hiểm " + dlnhanvien.SelectedItem.Text);
                        p[2].SqlDbType = SqlDbType.NVarChar;
                        p[3] = new SqlParameter("@DonVi", ex.GetGuid(dlBenBanBH.SelectedValue));
                        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[4] = new SqlParameter("@SoPC", sophieu);
                        p[4].SqlDbType = SqlDbType.Int;
                        p[5] = new SqlParameter("@TaiKhoan", DBNull.Value);
                        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[6] = new SqlParameter("@Del", 1);
                        p[6].SqlDbType = SqlDbType.Int;
                        p[7] = new SqlParameter("@LoaiCongNo", 1);
                        p[7].SqlDbType = SqlDbType.Int;
                        p[8] = new SqlParameter("@SoTien", double.Parse(txtthanhtiencongtynop.Text.Trim().Replace(",", "").Equals("") ? "0" : txtthanhtiencongtynop.Text.Trim()));
                        p[8].SqlDbType = SqlDbType.Float;
                        p[9] = new SqlParameter("@IDLenhChi", id);
                        p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[10] = new SqlParameter("@NhanVien", DBNull.Value);
                        p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaphieuchicongno_Add", p);
                    }
                    else {
                        int sophieu;
                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                        p[0].SqlDbType = SqlDbType.SmallDateTime;
                        dt = ex.GetData("sp_xdagetsophieuchiconnotravat_TienMat", p);
                        int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                        if (sopc == 0)
                            sophieu = 1;
                        else sophieu = sopc + 1;    
                        p = new SqlParameter[11];
                        p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                        p[1].SqlDbType = SqlDbType.SmallDateTime;
                        p[2] = new SqlParameter("@NoiDung", "Thanh toán tiền mua bảo hiểm " + dlnhanvien.SelectedItem.Text);
                        p[2].SqlDbType = SqlDbType.NVarChar;
                        p[3] = new SqlParameter("@DonVi", ex.GetGuid(dlBenBanBH.SelectedValue));
                        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[4] = new SqlParameter("@SoPC", sophieu);
                        p[4].SqlDbType = SqlDbType.Int;
                        p[5] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[6] = new SqlParameter("@Del", 1);
                        p[6].SqlDbType = SqlDbType.Int;
                        p[7] = new SqlParameter("@LoaiCongNo", 2);
                        p[7].SqlDbType = SqlDbType.Int;
                        p[8] = new SqlParameter("@SoTien",double.Parse(txtthanhtiencongtynop.Text.Trim().Replace(",", "").Equals("") ? "0" : txtthanhtiencongtynop.Text.Trim()));
                        p[8].SqlDbType = SqlDbType.Float;
                        p[9] = new SqlParameter("@IDLenhChi", id);
                        p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[10] = new SqlParameter("@NhanVien", DBNull.Value);
                        p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaphieuchicongno_Add", p);
                    }
                }

                      btnCancel_Click(sender, e);
                        gstGetMess("Lưu thành công", "");
                    }
                    catch (Exception ax)
                    {
                        gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                        ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tMua bảo hiểm nhân viên\t" + ax.Message);
                    }
                }
                else
                    gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        dlnhanvien.SelectedIndex = 0;
        txtluongcoban.Text = "";
        dlBenBanBH.SelectedIndex = 0;
        txtphucap.Text = "";
        txttongluong.Text = "";
        txtthanhtienbaohiem.Text = "";
        txtphantrambaohiem.Text = "";
        txtphantramcongty.Text = "";
        txtphantramnguoilaodong.Text = "";
        txtthanhtiencongtynop.Text = "";
        txtthanhtiennguoilaodongnop.Text = "";
        txtngaythang.Text = "";
        txtngaythu.Text = "";
        txtTuNgay.Text = "";
        txtDenNgay.Text = "";
        ckDaThu.Checked = false;
        ckDaTra.Checked = false;
        drkhach.SelectedIndex = 0;
        drhinhthucthanhtoan.SelectedIndex = 0;
        drtaikhoan.SelectedIndex = 0;
        //p = new SqlParameter[2];
        //p[0] = new SqlParameter("@PageNumber", 1);
        //p[0].SqlDbType = SqlDbType.Int;
        //p[1] = new SqlParameter("@RowspPage", PageNumber);
        //p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdamuabaohiemnhanvien_List");
        GV.DataBind();
        btnSave.Visible = true;
        btncapnhat.Visible = false;


    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        hdID.Value = ID;
        Guid gID = new Guid(ID);
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDs", gID);
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;

        if (e.CommandName.Equals("Select"))
        {
            DataTable dt = ex.GetData("sp_xdamuabaohiemnhanvien_ListByID", p);
            if (dt.Rows.Count > 0)
            {
                try
                {

                    dlnhanvien.SelectedValue = dt.Rows[0]["nhanvien"].ToString();
                    txtluongcoban.Text = dt.Rows[0]["luongcoban"].ToString();
                    dlBenBanBH.SelectedValue = dt.Rows[0]["donviban"].ToString();
                    txtphucap.Text = dt.Rows[0]["phucap"].ToString();
                    txttongluong.Text = dt.Rows[0]["tongluong"].ToString();
                    txtphantrambaohiem.Text = dt.Rows[0]["phantrambaohiem"].ToString();
                    txtthanhtienbaohiem.Text = dt.Rows[0]["thanhtienbaohiem"].ToString();
                    txtphantramcongty.Text = dt.Rows[0]["phantramcongtynop"].ToString();
                    txtphantramnguoilaodong.Text = dt.Rows[0]["phantramnhanviennop"].ToString();
                    txtthanhtiennguoilaodongnop.Text = dt.Rows[0]["thanhtiennhanviennop"].ToString();
                    txtthanhtiencongtynop.Text = dt.Rows[0]["thanhtiencongtynop"].ToString();
                    txtTuNgay.Text = dt.Rows[0]["tungay"].ToString();
                    txtDenNgay.Text = dt.Rows[0]["denngay"].ToString();
                    txtngaythang.Text = dt.Rows[0]["ngaynop"].ToString();
                    txtngaythu.Text = dt.Rows[0]["ngaythu"].ToString();
                    drhinhthucthanhtoan.SelectedValue = dt.Rows[0]["hinhthuc"].ToString();
                    drtaikhoan.SelectedValue = dt.Rows[0]["taikhoan"].ToString();
                    ckDaTra.Checked = dt.Rows[0]["datra"].ToString().Trim().Equals("1");
                    ckDaThu.Checked = dt.Rows[0]["dathu"].ToString().Trim().Equals("1");
                    hdatra.Value = dt.Rows[0]["datra"].ToString();
                    if (hdatra.Value.Equals("1"))
                        ckDaTra.Checked = true;
                    else ckDaTra.Checked = false;
                    hdathu.Value = dt.Rows[0]["dathu"].ToString();

                    if (hdathu.Value.Equals("1"))
                        ckDaThu.Checked = true;
                    else ckDaThu.Checked = false;
                }
                catch (Exception ax)
                {
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tMua bảo hiểm tài sản\t" + ax.Message);
                    gstGetMess(ax.Message, "");
                }

            }
            btncapnhat.Visible = true;
            btnSave.Visible = false;
        }
        else
            if (e.CommandName.Equals("Del"))
            {
                if (f_Xoa.Equals(1))
                {
                    try
                    {
                        ex.ExecuteNonQuerry("sp_xdamuabaohiemnhanvien_Del", p);
                        btnCancel_Click(sender, e);
                        gstGetMess("Xóa thành công", "");
                    }
                    catch (Exception ax)
                    {
                        ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tMua bảo hiểm tài sản\t" + ax.Message);
                        gstGetMess(ax.Message, "");
                    }
                }
                else
                    gstGetMess("Bạn không có quyền xóa dữ liệu trên trang này", "");
            }
    }
    protected void btncapnhat_Click(object sender, EventArgs e)
    {
        if (f_Sua.Equals(1))
        {
            try
            {
                int dachi;
                Guid gID = new Guid(hdID.Value);

                dachi = Int16.Parse(hdathu.Value);

                if (dachi == 1 && ckDaThu.Checked == true)
                {

                    p = new SqlParameter[8];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                    p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[2] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[2].SqlDbType = SqlDbType.SmallDateTime;
                    p[3] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[3].SqlDbType = SqlDbType.Float;
                    p[4] = new SqlParameter("@NoiDung", DBNull.Value);
                    p[4].SqlDbType = SqlDbType.NVarChar;

                    p[5] = new SqlParameter("@NgayTao", DateTime.Now);
                    p[5].SqlDbType = SqlDbType.SmallDateTime;
                    p[6] = new SqlParameter("@NguoiTao", Session["user"].ToString());
                    p[6].SqlDbType = SqlDbType.NVarChar;
                    p[7] = new SqlParameter("@Del", 1);
                    p[7].SqlDbType = SqlDbType.Int;

                    ex.ExecuteNonQuerry("sp_xdathietlapphieuthutienmat_Update_ThuongLuong", p);
                    p = new SqlParameter[3];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[2].SqlDbType = SqlDbType.Float;
                    ex.ExecuteNonQuerry("sp_ThietLapPhieuthuTienMat_UpDate_PCCongNo_ThuongLUong", p);
                    p = new SqlParameter[2];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NoiDung","Nộp lại tiền phải nộp mua bảo hiểm cho nhân viên");
                    p[1].SqlDbType = SqlDbType.NVarChar;
                    ex.ExecuteNonQuerry("sp_ThietLapPhieuthuTienMat_UpDate_MoiDungPC_ThuongLUong", p);

                }
                else if (dachi == 1 && ckDaThu.Checked == false)
                {
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAPhat_DelPhieuChi", p);
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAPhat_DelThietLap", p);
                }
                else if (dachi == 2 && ckDaThu.Checked == true)
                {
                    Guid tlchi = Guid.NewGuid();
                    p = new SqlParameter[9];
                    p[0] = new SqlParameter("@IDs", tlchi);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                    p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[2] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[2].SqlDbType = SqlDbType.SmallDateTime;
                    p[3] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[3].SqlDbType = SqlDbType.Float;
                    p[4] = new SqlParameter("@NoiDung", DBNull.Value);
                    p[4].SqlDbType = SqlDbType.NVarChar;

                    p[5] = new SqlParameter("@NgayTao", DateTime.Now);
                    p[5].SqlDbType = SqlDbType.SmallDateTime;
                    p[6] = new SqlParameter("@NguoiTao", Session["user"].ToString());
                    p[6].SqlDbType = SqlDbType.NVarChar;
                    p[7] = new SqlParameter("@Del", 1);
                    p[7].SqlDbType = SqlDbType.Int;
                    p[8] = new SqlParameter("@IDThanhToan", gID);
                    p[8].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAThietLapPhieuThuTienMat_Add", p);




                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NoiDung", "Nộp lại tiền mua bảo hiểm cho công ty");
                    p[1].SqlDbType = SqlDbType.NVarChar;
                    p[2] = new SqlParameter("@IDPhieuThu", tlchi);
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@Del", 1);
                    p[3].SqlDbType = SqlDbType.Int;
                    ex.ExecuteNonQuerry("sp_xdanoidungthutienmat_Add", p);

                    int sophieu;
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    dt = ex.GetData("sp_xdagetsophieuthuconnotravat_TienMat", p);
                    int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                    if (sopc == 0)
                        sophieu = 1;
                    else sophieu = sopc + 1;
                    p = new SqlParameter[11];
                    p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@NoiDung", DBNull.Value);
                    p[2].SqlDbType = SqlDbType.NVarChar;
                    p[3] = new SqlParameter("@DonVi", DBNull.Value);
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@SoPC", sophieu);
                    p[4].SqlDbType = SqlDbType.Int;
                    p[5] = new SqlParameter("@TaiKhoan", DBNull.Value);
                    p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[6] = new SqlParameter("@Del", 1);
                    p[6].SqlDbType = SqlDbType.Int;
                    p[7] = new SqlParameter("@LoaiCongNo", 2);
                    p[7].SqlDbType = SqlDbType.Int;
                    p[8] = new SqlParameter("@SoTien", double.Parse(txtthanhtiennguoilaodongnop.Text.Trim().Equals("") ? "0" : txtthanhtiennguoilaodongnop.Text.Trim()));
                    p[8].SqlDbType = SqlDbType.Float;
                    p[9] = new SqlParameter("@IDLenhChi", tlchi);
                    p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[10] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                    p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_XDAPhieuChiCongNoThu_Add", p);
                }


                if (hdatra.Value.Equals("1") && ckDaTra.Checked == true)
                {

                    p = new SqlParameter[3];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@SoTien", double.Parse(txtthanhtiencongtynop.Text.Trim().Equals("") ? "0" : txtthanhtiencongtynop.Text.Trim()));
                    p[2].SqlDbType = SqlDbType.Float;
                    ex.ExecuteNonQuerry("sp_xdamuabaohiemtaisan_Update_PhieuChi", p);

                }

                else if (hdatra.Value.Equals("1") && ckDaTra.Checked == false)
                {
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_xdamuabaohiemtaisan_Delte_PhieuChi", p);

                }

                else if (hdatra.Value.Equals("2") && ckDaTra.Checked == true)
                {
                    if (drhinhthucthanhtoan.SelectedIndex == 1)
                    {
                        int sophieu;
                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythu.Text.Trim()));
                        p[0].SqlDbType = SqlDbType.SmallDateTime;
                        dt = ex.GetData("sp_xdagetsophieuchiconnotravat_NganHang", p);
                        int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                        if (sopc == 0)
                            sophieu = 1;
                        else sophieu = sopc + 1;

                        p = new SqlParameter[11];
                        p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythu.Text.Trim()));
                        p[1].SqlDbType = SqlDbType.SmallDateTime;
                        p[2] = new SqlParameter("@NoiDung", "Thanh toán tiền mua bảo hiểm " + dlnhanvien.SelectedItem.Text);
                        p[2].SqlDbType = SqlDbType.NVarChar;
                        p[3] = new SqlParameter("@DonVi", ex.GetGuid(dlBenBanBH.SelectedValue));
                        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[4] = new SqlParameter("@SoPC", sophieu);
                        p[4].SqlDbType = SqlDbType.Int;
                        p[5] = new SqlParameter("@TaiKhoan", DBNull.Value);
                        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[6] = new SqlParameter("@Del", 1);
                        p[6].SqlDbType = SqlDbType.Int;
                        p[7] = new SqlParameter("@LoaiCongNo", 1);
                        p[7].SqlDbType = SqlDbType.Int;
                        p[8] = new SqlParameter("@SoTien", double.Parse(txtthanhtiencongtynop.Text.Trim().Replace(",", "").Equals("") ? "0" : txtthanhtiencongtynop.Text.Trim()));
                        p[8].SqlDbType = SqlDbType.Float;
                        p[9] = new SqlParameter("@IDLenhChi", gID);
                        p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[10] = new SqlParameter("@NhanVien", DBNull.Value);
                        p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaphieuchicongno_Add", p);
                    }
                    else
                    {
                        int sophieu;
                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                        p[0].SqlDbType = SqlDbType.SmallDateTime;
                        dt = ex.GetData("sp_xdagetsophieuchiconnotravat_TienMat", p);
                        int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                        if (sopc == 0)
                            sophieu = 1;
                        else sophieu = sopc + 1;
                        p = new SqlParameter[11];
                        p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythang.Text.Trim()));
                        p[1].SqlDbType = SqlDbType.SmallDateTime;
                        p[2] = new SqlParameter("@NoiDung", "Thanh toán tiền mua bảo hiểm " + dlnhanvien.SelectedItem.Text);
                        p[2].SqlDbType = SqlDbType.NVarChar;
                        p[3] = new SqlParameter("@DonVi", ex.GetGuid(dlBenBanBH.SelectedValue));
                        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[4] = new SqlParameter("@SoPC", sophieu);
                        p[4].SqlDbType = SqlDbType.Int;
                        p[5] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[6] = new SqlParameter("@Del", 1);
                        p[6].SqlDbType = SqlDbType.Int;
                        p[7] = new SqlParameter("@LoaiCongNo", 2);
                        p[7].SqlDbType = SqlDbType.Int;
                        p[8] = new SqlParameter("@SoTien", double.Parse(txtthanhtiencongtynop.Text.Trim().Replace(",", "").Equals("") ? "0" : txtthanhtiencongtynop.Text.Trim()));
                        p[8].SqlDbType = SqlDbType.Float;
                        p[9] = new SqlParameter("@IDLenhChi", gID);
                        p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                        p[10] = new SqlParameter("@NhanVien", DBNull.Value);
                        p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaphieuchicongno_Add", p);
                    }
                }



                p = new SqlParameter[18];
                p[0] = new SqlParameter("@IDs", gID);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@NhanVien", ex.GetGuid(dlnhanvien.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@LuongCoBan", double.Parse(txtluongcoban.Text.Trim().Replace(",", "").Equals("") ? "0" : txtluongcoban.Text.Trim().Replace(",", "")));
                p[2].SqlDbType = SqlDbType.Float;
                p[3] = new SqlParameter("@PhuCap", double.Parse(txtphucap.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphucap.Text.Trim().Replace(",", "")));
                p[3].SqlDbType = SqlDbType.Float;
                p[4] = new SqlParameter("@PhanTramBaoHiem", double.Parse(txtphantrambaohiem.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantrambaohiem.Text.Trim().Replace(",", "")));
                p[4].SqlDbType = SqlDbType.Float;
                p[5] = new SqlParameter("@PhanTramCongTyNop", double.Parse(txtphantramcongty.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantramcongty.Text.Trim().Replace(",", "")));
                p[5].SqlDbType = SqlDbType.Float;
                p[6] = new SqlParameter("@PhanTramNhanVienNop", double.Parse(txtphantramnguoilaodong.Text.Trim().Replace(",", "").Equals("") ? "0" : txtphantramnguoilaodong.Text.Trim().Replace(",", "")));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@DonViBan", ex.GetGuid(dlBenBanBH.SelectedValue));
                p[7].SqlDbType = SqlDbType.UniqueIdentifier;
                p[8] = new SqlParameter("@DonViNhan", ex.GetGuid(drkhach.SelectedValue));
                p[8].SqlDbType = SqlDbType.UniqueIdentifier;
                p[9] = new SqlParameter("@NgayNop", ex.GetNgayThang(txtngaythang.Text.Trim()));
                p[9].SqlDbType = SqlDbType.SmallDateTime;
                p[10] = new SqlParameter("@HinhThuc", drhinhthucthanhtoan.SelectedValue);
                p[10].SqlDbType = SqlDbType.Int;
                p[11] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                p[11].SqlDbType = SqlDbType.UniqueIdentifier;
                p[12] = new SqlParameter("@NgayThu", ex.GetNgayThang(txtngaythu.Text.Trim()));
                p[12].SqlDbType = SqlDbType.SmallDateTime;
                p[13] = new SqlParameter("@DaTra", ckDaTra.Checked ? 1 : 2);
                p[13].SqlDbType = SqlDbType.Int;
                p[14] = new SqlParameter("@DaThu", ckDaThu.Checked ? 1 : 2);
                p[14].SqlDbType = SqlDbType.Int;
                p[15] = new SqlParameter("@Del", 1);
                p[15].SqlDbType = SqlDbType.Int;
                p[16] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[16].SqlDbType = SqlDbType.SmallDateTime;
                p[17] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[17].SqlDbType = SqlDbType.SmallDateTime;
                ex.ExecuteNonQuerry("sp_xdamuabaohiemnhanvien_Update", p);

                btnCancel_Click(sender, e);
                gstGetMess("Cập nhật thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình cập nhật, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tMua bảo hiểm tài sản\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền sửa dữ liệu trên trang này", "");
    }
    void LoadTaiSan(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_ThietLapMuaBaoHiemNhanViem_ListNhanVien");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadNhanVien(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_ThietLapMuaBaoHiemNhanViem_ListNhanVien");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadBenBanBH(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_xdabenbanbh_LoadDrop");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn bên bán BH--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadNhaCungCap(DropDownList dl, string title)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_xdanhacungcap_LoadDrop");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem(title, ""));
        dl.SelectedIndex = 0;
    }

   

    protected void btnPrint_Click(object sender, EventArgs e)
    {
        dt = ex.GetData("sp_xdamuabaohiemtaisan_Print");
        PrintNPOI(dt);
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Báo cáo mua bảo hiểm tài sản";


            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.WrapText = true;
            headerLabelCellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.VerticalAlignment = VerticalAlignment.CENTER;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.VerticalAlignment = VerticalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;
            hs4.VerticalAlignment = VerticalAlignment.TOP;
            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;
            hs4.WrapText = true;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO MUA BẢO HIỂM TÀI SẢN " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 11);
            sheet.AddMergedRegion(cra);

            //Bộ phận
            rowIndex++;


            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2
            rowIndex++;
            rowIndex++;
            //header1
            row = sheet.CreateRow(rowIndex);
            string[] header1 = { "STT", "Tên tài sản", "Nhãn hiệu", "Đơn vị bán BH", "Hình thức mua BH", "Giá trị tài sản", "% Bảo hiểm", "Thành tiền BH sau thuế", "Thành tiền BH trước thuế", "% chiết khấu", "Thành tiền chiết khấu", "Từ ngày", "Đến ngày", "Đã nhận BH", "Đã nhận chiết khấu" };
            //set do rong cho cac cot
            int[] width = { 35 * 36, 70 * 36, 70 * 36, 115 * 36, 70 * 36, 77 * 36, 52 * 36, 84 * 36, 77 * 36, 58 * 36, 92 * 36, 71 * 36, 71 * 36, 60 * 36, 60 * 36 };
            //tao header1
            for (int m = 0; m <= 14; m++)
            {
                r1c1 = row.CreateCell(m);
                r1c1.SetCellValue(header1[m]);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;

                sheet.SetColumnWidth(m, width[m]);
                row.Height = (short)1150;
            }


            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue((i + 1).ToString());
                r1c1.CellStyle = hsCenter;

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    r1c1 = row.CreateCell(j + 1);
                    if (j >= 4 && j <= 9)
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            r1c1.SetCellType(CellType.NUMERIC);
                            r1c1.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        r1c1.CellStyle = hsRight;
                    }
                    else
                    {
                        r1c1.SetCellValue(dt.Rows[i][j].ToString());

                        if (j == 13 || j == 12)
                            r1c1.CellStyle = hsCenter;
                        else
                            r1c1.CellStyle = headerLabelCellStyle;

                    }
                    r1c1.Row.Height = 500;
                }
                row.Height = (short)667;

            }
            //for (int ik = 0; ik <= 14; ik++)
            //{
            //    sheet.AutoSizeColumn(ik);
            //}

            //end sheet tong hop--------------------------
            #region Footer
            //-----------Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            r1c1 = row.CreateCell(3);
            r1c1.SetCellValue("Tổng cộng");
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            //FHIK
            r1c1 = row.CreateCell(5);
            r1c1.CellFormula = "Sum(F7:F" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            r1c1 = row.CreateCell(7);
            r1c1.CellFormula = "Sum(H7:H" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            r1c1 = row.CreateCell(8);
            r1c1.CellFormula = "Sum(I7:I" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            r1c1 = row.CreateCell(10);
            r1c1.CellFormula = "Sum(K7:K" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            //fix lai border
            for (int h = 0; h <= 14; h++)
            {
                if (h == 3 || h == 5 || h == 7 || h == 8 || h == 10)
                {

                }
                else
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            #endregion
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string saveAsFileName = "BCMuaBHTaiSan.xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
        else
            gstGetMess("Không có bản ghi nào để in", "");
    }
    protected void ckDaThu_CheckedChanged(object sender, EventArgs e)
    {
        if (ckDaThu.Checked == true)
        {
            txtngaythu.Enabled = true;
           
        }
        else
        {
            txtngaythu.Enabled = false;
            
        }
    }
    void LoadTK()
    {
        drtaikhoan.Items.Clear();
        drtaikhoan.DataSource = ex.GetData("sp_ListTaiKhoan_KhongVAT");
        drtaikhoan.DataBind();
        drtaikhoan.Items.Insert(0, new ListItem("--Chọn tài khoản--", ""));
        drtaikhoan.SelectedIndex = 0;
    }
    protected void ckDaTra_CheckedChanged(object sender, EventArgs e)
    {
        if (ckDaTra.Checked == true)
        {
            txtngaythang.Enabled = true;
            drhinhthucthanhtoan.Enabled = true;
            drtaikhoan.Enabled = true;
        }
        else
        {
            txtngaythang.Enabled = false;
            drhinhthucthanhtoan.Enabled = false;
            drtaikhoan.Enabled = false;
        }
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@NhanVien",ex.GetGuid(DropDownList2.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        GV.DataSource = ex.GetData("sp_xdamuabaohiemnhanvien_List_TimKiem", p);
        GV.DataBind();
    }
}